![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
ParallelizationIf the Oracle Parallel Query option is used and the statement being executed is parallelizable, the following steps take place:
The degree of parallelism is determined using the following order of precedence:
The processes that execute the query are taken from the set of query servers available in the query server pool. This number is specified by the Oracle initialization parameter PARALLEL_MAX_SERVERS. Fetch Rows To Be ReturnedIf the statement is a query, the final step in the processing of the SQL statement involves fetching the returned data in a loop until all the requested data has been returned to the user process.
SummaryBadly formed SQL statements can nullify any optimizations you have made to the client or the server. If the SQL statements require unnecessary table scans or unnecessary processing, an optimized server can be brought to a standstill. To have the most-optimal system possible, each componentthe client, the SQL statements, the network, and the servershould be tuned to take maximum advantage of available resources. The best way to improve performance is to reduce unnecessary processing and I/O. You can do this by designing your SQL statements to take advantage of such features as the shared pool (where the library cache contains the shared SQL area). By designing your application so that it uses the same SQL statements to access the same data, you stand a good chance of your statements being in the library cache. If you have long-running queries that perform tables scans, take advantage of the Parallel Query option. By using this feature, you can cut down on the time it takes to perform the query. The use of other Oracle features such as stored procedures and packages can also maximize the effectiveness of your SQL statement processing and provide for more efficient coding. All these things can help improve the overall performance of your system. The remaining chapters in Part IV of this book look into ways you can make your SQL statements more efficientand how you can determine the efficiency of your SQL statements. Of all the areas of the system that can be tuned, SQL statements offer the most potential. This is true not because SQL statement can be more effectively optimized, but because a badly tuned SQL statement can degrade performance drastically. As you will see, it is very important to have well-tuned SQL statements at the heart of your application.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |